<?php

include "../lib/start.php";

check_session();
check_permission(ADM_PERM);

header("Content-type: text/plain");

$id_anno = $_POST['anno'];
$sel_anno = "SELECT * FROM anni WHERE id_anno = $id_anno";
try{
	$res_anno = $db->executeQuery($sel_anno);
} catch (MySQLException $ex){
	$er = "ko;".$ex->getQuery().";".$ex->getMessage();
	print $er;
	exit;
}
$_anno = $res_anno->fetch_assoc();
$anno = substr($_anno['data_fine'], 0, 4); 

$queries1q = array();
$queries2q = array();
array_push($queries1q, "DROP VIEW IF EXISTS non_sufficienti_".$anno."_1q");
array_push($queries1q, "CREATE VIEW non_sufficienti_".$anno."_1q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, alunno, id_alunno, id_classe, sesso FROM dati_".$anno."_1q GROUP BY  id_classe, sesso, alunno, id_alunno HAVING (AVG(voto) < 6 AND AVG(voto) > 0) ORDER BY id_classe;");
array_push($queries1q, "DROP VIEW IF EXISTS classifica_alunni_".$anno."_1q");
array_push($queries1q, "CREATE VIEW classifica_alunni_".$anno."_1q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, alunno, id_alunno, id_classe, sesso FROM dati_".$anno."_1q GROUP BY  id_classe, sesso, alunno, id_alunno ORDER BY id_classe;");
array_push($queries1q, "DROP VIEW IF EXISTS classifica_classi_".$anno."_1q");
array_push($queries1q, "CREATE VIEW classifica_classi_".$anno."_1q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, id_classe FROM dati_".$anno."_1q GROUP BY id_classe ORDER BY id_classe;");
array_push($queries1q, "DROP VIEW IF EXISTS classifica_classi_sesso_".$anno."_1q");
array_push($queries1q, "CREATE VIEW classifica_classi_sesso_".$anno."_1q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, id_classe, sesso FROM dati_".$anno."_1q GROUP BY id_classe, sesso ORDER BY id_classe, sesso");

array_push($queries2q, "DROP VIEW IF EXISTS non_classificati_".$anno."_2q");
array_push($queries2q, "CREATE VIEW non_classificati_".$anno."_2q AS select alunno, id_alunno, id_classe, sesso FROM dati_".$anno."_2q GROUP BY id_classe, sesso, alunno, id_alunno HAVING (AVG(voto) < 1) ORDER BY id_classe;");
array_push($queries2q, "DROP VIEW IF EXISTS non_ammessi_".$anno."_2q");
array_push($queries2q, "CREATE VIEW non_ammessi_".$anno."_2q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, alunno, id_alunno, id_classe, sesso FROM dati_".$anno."_2q GROUP BY  id_classe, sesso, alunno, id_alunno HAVING (AVG(voto) < 6 AND AVG(voto) > 0) ORDER BY id_classe;");
array_push($queries2q, "DROP VIEW IF EXISTS classifica_alunni_".$anno."_2q");
array_push($queries2q, "CREATE VIEW classifica_alunni_".$anno."_2q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, alunno, id_alunno, id_classe, sesso FROM dati_".$anno."_2q GROUP BY  id_classe, sesso, alunno, id_alunno ORDER BY id_classe;");
array_push($queries2q, "DROP VIEW IF EXISTS classifica_classi_".$anno."_2q");
array_push($queries2q, "CREATE VIEW classifica_classi_".$anno."_2q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, id_classe FROM dati_".$anno."_2q GROUP BY id_classe ORDER BY id_classe;");
array_push($queries2q, "DROP VIEW IF EXISTS classifica_classi_sesso_".$anno."_2q");
array_push($queries2q, "CREATE VIEW classifica_classi_sesso_".$anno."_2q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg, id_classe, sesso FROM dati_".$anno."_2q GROUP BY id_classe, sesso ORDER BY id_classe, sesso");

$sel_materie = "SELECT * FROM materie WHERE has_sons = 0 AND id_materia < 20 AND id_materia != 11 AND id_materia != 10";
try{
	$res_materie = $db->executeQuery($sel_materie);
} catch(MySQLException $ex){
	$er = "ko;".$ex->getQuery().";".$ex->getMessage();
	print $er;
	exit;
}
while($m = $res_materie->fetch_assoc()){
	$mat = strtolower($m['materia']);
	$mat_q = strtoupper($mat);
	$mat = preg_replace("/ /", "_", $mat);
	array_push($queries1q, "DROP VIEW IF EXISTS classifica_".$mat."_".$anno."_1q");
	array_push($queries1q, "CREATE VIEW classifica_".$mat."_".$anno."_1q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg,  id_classe FROM dati_".$anno."_1q WHERE materia = '$mat_q' GROUP BY  id_classe ORDER BY id_classe");
	array_push($queries1q, "DROP VIEW IF EXISTS classifica_".$mat."_sesso_".$anno."_1q");
	array_push($queries1q, "CREATE VIEW classifica_".$mat."_sesso_".$anno."_1q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg,  id_classe, sesso FROM dati_".$anno."_1q WHERE materia = '$mat_q' GROUP BY  id_classe, sesso ORDER BY id_classe");
	array_push($queries2q, "DROP VIEW IF EXISTS classifica_".$mat."_".$anno."_2q");
	array_push($queries2q, "CREATE VIEW classifica_".$mat."_".$anno."_2q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg,  id_classe FROM dati_".$anno."_2q WHERE materia = '$mat_q' GROUP BY  id_classe ORDER BY id_classe");
	array_push($queries2q, "DROP VIEW IF EXISTS classifica_".$mat."_sesso_".$anno."_2q");
	array_push($queries2q, "CREATE VIEW classifica_".$mat."_sesso_".$anno."_2q AS select SUM(voto) AS sum, ROUND(AVG(voto), 2) AS round, AVG(voto) AS avg,  id_classe, sesso FROM dati_".$anno."_2q WHERE materia = '$mat_q' GROUP BY  id_classe, sesso ORDER BY id_classe");
}

$ok = $ko = 0;

if($_REQUEST['quad'] == 0){
	$num_queries = count($queries1q) + count($queries2q);
	foreach ($queries1q as $q){
		try {
			$res = $db->executeQuery($q);
		} catch(MySQLException $ex){
			$ko++;
		}

	}
	foreach ($queries2q as $q){
		try {
			$res = $db->executeQuery($q);
		} catch (MySQLException $ex){
			$ko++;
		}
	}
}
else if($_REQUEST['quad'] == 1) {
	$num_queries = count($queries1q);
	foreach ($queries1q as $q){
		try {
			$res = $db->executeQuery($q);
		} catch (MySQLException $ex){
			$ko++;
		}
	}
}
else if($_REQUEST['quad'] == 2) {
	$num_queries = count($queries2q);
	foreach ($queries2q as $q){
		try {
			$res = $db->executeQuery($q);
		} catch (MySQLException $ex){
			$ko++;
		}
	}
}

$ok = $num_queries - $ko;

$msg = "Operazione terminata. Create correttamente $ok viste su $num_queries. ";
if($ko > 0)
	$msg += "Si sono verificati $ko errori";
print $msg;
exit;

?>